package com.yyzzzz.poi.service;

import com.github.crab2died.ExcelUtils;
import com.yyzzzz.poi.domain.Video;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;

/**
 * Created by yyzzzz on 2018/12/9.
 */
public class SchoolService {

    public static void main(String[] args) {
        String path = "C:\\Users\\yyzzzz\\Desktop\\12-09.xlsx";
        try {

            List<Video> videos = ExcelUtils.getInstance().readExcel2Objects(path, Video.class, 0, 0);
            System.out.println("读取Excel至对象数组(支持类型转换)：");
            for (Video video : videos) {
                insertVideo(video);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void insertVideo(Video video) {

        //连接对象
        Connection conn = null;
        //SQL语句执行对象
        PreparedStatement ps = null;
        // 加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接,localhost为主机IP地址（本机），3306为mysql的端口号，
            //testdb为数据库的库名,characterEncoding=utf-8为字节编码集
            //root为mysql的登录名，123456为mysql的登录密码
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/poi_test?characterEncoding=utf-8&useSSL=false",
                    "root", "1230");

            //执行SQL语句，？为占位符
            ps = conn.prepareStatement("insert into school(name,type,jingdu,weidu) values(?,?,?,?)");
            //填充占位符
            ps.setString(1, video.getShopSign());
            ps.setString(2, video.getType());
            ps.setString(3, "120°37′");
            ps.setString(4, "31°19′");
            //更新数据库
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//关闭连接
            try {
                ps.close();
                conn.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}
